##################################################################################################################
# Code for Results in Appendix Sections A5.2, A5.3, and A5.4
##################################################################################################################
write.table(data.frame(Description=c("","","","Appendix Section A5.2: Unweighted vs. Weighted Correlations")),row.names=F,col.names=F,quote=F)

##################################################################################################################
# To run, please set the working directory to match the location of the folder containing all replication files.
##################################################################################################################
#setwd("Replication_PSRM_ST24")

#if not yet created, create "output" folder
if("output"%in%list.files()==F){dir.create("output")}

#Clear environment
rm(list=ls());gc();gc();gc();gc()

#If not yet installed, install packages
if(length(find.package("dplyr",quiet=T))==0){install.packages("dplyr")}
if(length(find.package("Hmisc",quiet=T))==0){install.packages("Hmisc")}
if(length(find.package("weights",quiet=T))==0){install.packages("weights")}
if(length(find.package("knitr",quiet=T))==0){install.packages("knitr")} 
if(as.numeric(available.packages()["knitr","Version"])<1.48){install.packages("knitr")} #updated version required to prevent error with kbl(x,format="latex)
if(length(find.package("kableExtra",quiet=T))==0){install.packages("kableExtra")}

#Load package
library(dplyr)
library(weights) #loads required package Hmisc
library(kableExtra)

#Set POSIX locale
Sys.setlocale(locale="C")

#Load main data set
loball5ciA<-readRDS("Data/PSRM_main_dataset.rds")

#####
#within dyad deviations

#contract and in-house
agg67c<-loball5ciA %>%
  filter(type%in%c("contract","inhouse")==T)%>%
  group_by(principal_cleaned,lobname_cleaned, halfyear,type,session,both_ever_count1,
           inhouse_session_count1,contract_session_count1,organization_type) %>% 
  summarise(.groups="keep",sum_hours=sum(hours,na.rm=T),sum_amount=sum(Rcomparable_total_amounts_cleaned_exp,na.rm=T))

#get total amount of compensation (by dyad) in biennium
agg67c <- agg67c %>% group_by(lobname_cleaned,halfyear) %>%
  mutate(count_client=length(unique(principal_cleaned)))


#contract and in-house
agg67d2<-agg67c
#create hours worked variable so that later log transformation puts zero weight on extensive margin
agg67d2$sum_hours_cr0<-agg67d2$sum_hours
agg67d2$sum_hours_cr0[agg67d2$sum_hours_cr0>0]<-agg67d2$sum_hours_cr0[agg67d2$sum_hours_cr0>0]/min(agg67d2$sum_hours_cr0[agg67d2$sum_hours_cr0>0])
agg67d2$sum_hours_cr0[agg67d2$sum_hours_cr0==0]<-1

#create variables for analyses
agg67d2 <-agg67d2 %>%  
  filter(sum_amount>0) %>% 
  group_by(principal_cleaned,lobname_cleaned,session,type) %>%
  mutate(
    dif_log_mean_amount=log(sum_amount)-log(mean(sum_amount,na.rm=T)),
    dif_log_mean_hours_cr0=log(sum_hours_cr0)-log(mean(sum_hours_cr0,na.rm=T))
  )

agg67d2$count<-1
agg67d2 <-agg67d2 %>% 
  group_by(session) %>%
  mutate(all_biennium_exp=sum(sum_amount,na.rm=T)) 
agg67d2 <-agg67d2 %>% 
  group_by(principal_cleaned,lobname_cleaned,session,type) %>%
  mutate(all_biennium_principal_exp=sum(sum_amount,na.rm=T),num_dyad_obs=sum(count)) 
#exclude observations when num_dyad_obs==1
agg67d2<-agg67d2[which(agg67d2$num_dyad_obs>1),]

agg67d2$pct_exp<-agg67d2$all_biennium_principal_exp/agg67d2$all_biennium_exp

###contract
agg6c<-loball5ciA %>%
  filter(type%in%c("contract")==T)%>%
  group_by(principal_cleaned,lobname_cleaned, halfyear,session,both_ever_count1,
           inhouse_session_count1,contract_session_count1,organization_type) %>% 
  summarise(.groups="keep",sum_hours=sum(hours,na.rm=T),sum_amount=sum(Rcomparable_total_amounts_cleaned_exp,na.rm=T))

#get total amount of compensation (by dyad) in biennium
agg6c <- agg6c %>% group_by(lobname_cleaned,halfyear) %>%
  mutate(count_client=length(unique(principal_cleaned)))

agg6d2<-agg6c
#create hours worked variable so that later log transformation puts zero weight on extensive margin
agg6d2$sum_hours_cr0<-agg6d2$sum_hours
agg6d2$sum_hours_cr0[agg6d2$sum_hours_cr0>0]<-agg6d2$sum_hours_cr0[agg6d2$sum_hours_cr0>0]/min(agg6d2$sum_hours_cr0[agg6d2$sum_hours_cr0>0])
agg6d2$sum_hours_cr0[agg6d2$sum_hours_cr0==0]<-1

#create variables for analyses
agg6d2 <-agg6d2 %>%  
  filter(sum_amount>0) %>% 
  group_by(principal_cleaned,lobname_cleaned,session) %>%
  mutate(
    dif_log_mean_amount=log(sum_amount)-log(mean(sum_amount,na.rm=T)),
    dif_log_mean_hours_cr0=log(sum_hours_cr0)-log(mean(sum_hours_cr0,na.rm=T))
  )

agg6d2$count<-1
agg6d2 <-agg6d2 %>% 
  group_by(session) %>%
  mutate(all_biennium_exp=sum(sum_amount,na.rm=T)) 
agg6d2 <-agg6d2 %>% 
  group_by(principal_cleaned,lobname_cleaned,session) %>%
  mutate(all_biennium_principal_exp=sum(sum_amount,na.rm=T),num_dyad_obs=sum(count)) 
#exclude observations when num_dyad_obs==1
agg6d2<-agg6d2[which(agg6d2$num_dyad_obs>1),]

agg6d2$pct_exp<-agg6d2$all_biennium_principal_exp/agg6d2$all_biennium_exp

#####in-house
agg7c<-loball5ciA %>%
  filter(type%in%c("inhouse")==T)%>%
  group_by(principal_cleaned,lobname_cleaned,halfyear,session,both_ever_count1,
           inhouse_session_count1,contract_session_count1,organization_type) %>% 
  summarise(.groups="keep",sum_hours=sum(hours,na.rm=T),sum_amount=sum(Rcomparable_total_amounts_cleaned_exp,na.rm=T))

#get number of clients for whom lobbyist is working in halfyear
agg7c <- agg7c %>% group_by(lobname_cleaned,halfyear) %>%
  mutate(count_client=length(unique(principal_cleaned)))

agg7d2<-agg7c
#create hours worked variable so that later log transformation puts zero weight on extensive margin
agg7d2$sum_hours_cr0<-agg7d2$sum_hours
agg7d2$sum_hours_cr0[agg7d2$sum_hours_cr0>0]<-agg7d2$sum_hours_cr0[agg7d2$sum_hours_cr0>0]/min(agg7d2$sum_hours_cr0[agg7d2$sum_hours_cr0>0])
agg7d2$sum_hours_cr0[agg7d2$sum_hours_cr0==0]<-1

#create variables for analyses
agg7d2 <-agg7d2 %>%  
  filter(sum_amount>0) %>% 
  group_by(principal_cleaned,lobname_cleaned,session) %>%
  mutate(
    dif_log_mean_amount=log(sum_amount)-log(mean(sum_amount,na.rm=T)),
    dif_log_mean_hours_cr0=log(sum_hours_cr0)-log(mean(sum_hours_cr0,na.rm=T))
  )

agg7d2$count<-1
agg7d2 <-agg7d2 %>% 
  group_by(session) %>%
  mutate(all_biennium_exp=sum(sum_amount,na.rm=T)) 
agg7d2 <-agg7d2 %>% 
  group_by(principal_cleaned,lobname_cleaned,session) %>%
  mutate(all_biennium_principal_exp=sum(sum_amount,na.rm=T),num_dyad_obs=sum(count)) 

#exclude observations when num_dyad_obs==1
agg7d2<-agg7d2[which(agg7d2$num_dyad_obs>1),]

agg7d2$pct_exp<-agg7d2$all_biennium_principal_exp/agg7d2$all_biennium_exp

############################################################
############ Aggregated to yearly
############################################################

#contract and in-house, yearly
agg67d3<-agg67c %>%
  group_by(principal_cleaned,lobname_cleaned,year=substr(halfyear,1,4),session, type,both_ever_count1,
           inhouse_session_count1,contract_session_count1,organization_type) %>%
  summarise(.groups="keep",sum_hours=sum(sum_hours,na.rm=T),sum_amount=sum(sum_amount,na.rm=T))

#create hours worked variable so that later log transformation puts zero weight on extensive margin
agg67d3$sum_hours_cr0<-agg67d3$sum_hours
agg67d3$sum_hours_cr0[agg67d3$sum_hours_cr0>0]<-agg67d3$sum_hours_cr0[agg67d3$sum_hours_cr0>0]/min(agg67d3$sum_hours_cr0[agg67d3$sum_hours_cr0>0])
agg67d3$sum_hours_cr0[agg67d3$sum_hours_cr0==0]<-1

#create variables for analyses
agg67d3 <-agg67d3 %>%  
  filter(sum_amount>0) %>% 
  group_by(principal_cleaned,lobname_cleaned,type) %>%
  mutate(
        dif_log_mean_amount=log(sum_amount)-log(mean(sum_amount,na.rm=T)),
        dif_log_mean_hours_cr0=log(sum_hours_cr0)-log(mean(sum_hours_cr0,na.rm=T))
        )

agg67d3$count<-1
agg67d3 <-agg67d3 %>% group_by(count) %>%
  mutate(all_exp=sum(sum_amount,na.rm=T)) 
agg67d3 <-agg67d3 %>% group_by(principal_cleaned,lobname_cleaned,type) %>%
  mutate(all_principal_lob_exp=sum(sum_amount,na.rm=T)) 
agg67d3 <-agg67d3 %>% group_by(principal_cleaned,lobname_cleaned,type) %>%
  mutate(num_dyad_obs=sum(count)) 
#exclude observations when num_dyad_obs==1
agg67d3<-agg67d3[which(agg67d3$num_dyad_obs>1),]

agg67d3$pct_exp<-agg67d3$all_principal_lob_exp/agg67d3$all_exp

agg67d3<-agg67d3[order(agg67d3$principal_cleaned,agg67d3$lobname_cleaned,as.numeric(agg67d3$year),agg67d3$type),]
agg67d3<-agg67d3 %>% group_by(principal_cleaned,lobname_cleaned,type) %>%
  mutate(nyears=cumsum(count))

###contract lobbyists, yearly
agg6d3<-agg6c %>%
  group_by(principal_cleaned,lobname_cleaned,year=substr(halfyear,1,4),session, both_ever_count1,
           inhouse_session_count1,contract_session_count1,organization_type) %>%
  summarise(.groups="keep",sum_hours=sum(sum_hours,na.rm=T),sum_amount=sum(sum_amount,na.rm=T))

#create hours worked variable so that later log transformation puts zero weight on extensive margin
agg6d3$sum_hours_cr0<-agg6d3$sum_hours
agg6d3$sum_hours_cr0[agg6d3$sum_hours_cr0>0]<-agg6d3$sum_hours_cr0[agg6d3$sum_hours_cr0>0]/min(agg6d3$sum_hours_cr0[agg6d3$sum_hours_cr0>0])
agg6d3$sum_hours_cr0[agg6d3$sum_hours_cr0==0]<-1

#create variables for analyses
agg6d3 <-agg6d3 %>%  
  filter(sum_amount>0) %>% 
  group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(
    dif_log_mean_amount=log(sum_amount)-log(mean(sum_amount,na.rm=T)),
    dif_log_mean_hours_cr0=log(sum_hours_cr0)-log(mean(sum_hours_cr0,na.rm=T))
  )

agg6d3$count<-1
agg6d3 <-agg6d3 %>% group_by(count) %>%
  mutate(all_exp=sum(sum_amount,na.rm=T)) 
agg6d3 <-agg6d3 %>% group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(all_principal_lob_exp=sum(sum_amount,na.rm=T)) 
agg6d3 <-agg6d3 %>% group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(num_dyad_obs=sum(count)) 

#exclude observations when num_dyad_obs==1
agg6d3<-agg6d3[which(agg6d3$num_dyad_obs>1),]

agg6d3$pct_exp<-agg6d3$all_principal_lob_exp/agg6d3$all_exp

agg6d3<-agg6d3[order(agg6d3$principal_cleaned,agg6d3$lobname_cleaned,as.numeric(agg6d3$year)),]
agg6d3<-agg6d3 %>% group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(nyears=cumsum(count))

###in-house lobbyists, yearly
agg7d3<-agg7c %>%
  group_by(principal_cleaned,lobname_cleaned,year=substr(halfyear,1,4),session, both_ever_count1,
           inhouse_session_count1,contract_session_count1,organization_type) %>%
  summarise(.groups="keep",sum_hours=sum(sum_hours,na.rm=T),sum_amount=sum(sum_amount,na.rm=T))

#create hours worked variable so that later log transformation puts zero weight on extensive margin
agg7d3$sum_hours_cr0<-agg7d3$sum_hours
agg7d3$sum_hours_cr0[agg7d3$sum_hours_cr0>0]<-agg7d3$sum_hours_cr0[agg7d3$sum_hours_cr0>0]/min(agg7d3$sum_hours_cr0[agg7d3$sum_hours_cr0>0])
agg7d3$sum_hours_cr0[agg7d3$sum_hours_cr0==0]<-1

#create variables for analyses
agg7d3 <-agg7d3 %>%  
  filter(sum_amount>0) %>% 
  group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(
    dif_log_mean_amount=log(sum_amount)-log(mean(sum_amount,na.rm=T)),
    dif_log_mean_hours_cr0=log(sum_hours_cr0)-log(mean(sum_hours_cr0,na.rm=T))
  )

agg7d3$count<-1
agg7d3 <-agg7d3 %>% group_by(count) %>%
  mutate(all_exp=sum(sum_amount,na.rm=T)) 
agg7d3 <-agg7d3 %>% group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(all_principal_lob_exp=sum(sum_amount,na.rm=T)) 
agg7d3 <-agg7d3 %>% group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(num_dyad_obs=sum(count)) 

#exclude observations when num_dyad_obs==1
agg7d3<-agg7d3[which(agg7d3$num_dyad_obs>1),]

agg7d3$pct_exp<-agg7d3$all_principal_lob_exp/agg7d3$all_exp

agg7d3<-agg7d3[order(agg7d3$principal_cleaned,agg7d3$lobname_cleaned,as.numeric(agg7d3$year)),]
agg7d3<-agg7d3 %>% group_by(principal_cleaned,lobname_cleaned) %>%
  mutate(nyears=cumsum(count))


### Create table including both weighted und unweighted correlations
table_a0<-as.data.frame(matrix(NA,nrow=12,ncol=4),stringsAsFactors = F)
colnames(table_a0)<-c("All","Contract","In-House","Description")
table_a0$Description<-c("Unweighted Observations,","Half-Yearly","N","Weighted Observations,","Half-Yearly","N",
                      "Unweighted Observations,","Yearly","N","Weighted Observations,","Yearly","N")

#half-yearly, contract and in-house, unweighted
ct1<-cor.test(agg67d2$dif_log_mean_amount,agg67d2$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a0[1,1]<-paste(round(ct1$estimate,3))
table_a0[2,1]<-paste(round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3))
table_a0[3,1]<-paste(ct1$parameter+2)
#half-yearly, contract, unweighted
ct1<-cor.test(agg6d2$dif_log_mean_amount,agg6d2$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a0[1,2]<-paste(round(ct1$estimate,3))
table_a0[2,2]<-paste(round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3))
table_a0[3,2]<-paste(ct1$parameter+2)
#half-yearly, in-house, unweighted
ct1<-cor.test(agg7d2$dif_log_mean_amount,agg7d2$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a0[1,3]<-paste(round(ct1$estimate,3))
table_a0[2,3]<-paste(round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3))
table_a0[3,3]<-paste(ct1$parameter+2)
#half-yearly, contract and in-house, weighted
ct1<-cor.test(agg67d2$dif_log_mean_amount,agg67d2$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
wtd.cor(x=agg67d2$dif_log_mean_amount,y=agg67d2$dif_log_mean_hours_cr0,weight=agg67d2$pct_exp,mean1=F)
table_a0[4,1]<-round(wtd.cor(x=agg67d2$dif_log_mean_amount,y=agg67d2$dif_log_mean_hours_cr0,weight=agg67d2$pct_exp,mean1=F)[1],3)
table_a0[5,1]<-round(wtd.cor(x=agg67d2$dif_log_mean_amount,y=agg67d2$dif_log_mean_hours_cr0,weight=agg67d2$pct_exp,mean1=F)[2],3)
table_a0[6,1]<-paste(ct1$parameter+2)
#half-yearly, contract, weighted
ct1<-cor.test(agg6d2$dif_log_mean_amount,agg6d2$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
wtd.cor(x=agg6d2$dif_log_mean_amount,y=agg6d2$dif_log_mean_hours_cr0,weight=agg6d2$pct_exp,mean1=F)
table_a0[4,2]<-round(wtd.cor(x=agg6d2$dif_log_mean_amount,y=agg6d2$dif_log_mean_hours_cr0,weight=agg6d2$pct_exp,mean1=F)[1],3)
table_a0[5,2]<-round(wtd.cor(x=agg6d2$dif_log_mean_amount,y=agg6d2$dif_log_mean_hours_cr0,weight=agg6d2$pct_exp,mean1=F)[2],3)
table_a0[6,2]<-paste(ct1$parameter+2)
#half-yearly, in-house, weighted
ct1<-cor.test(agg7d2$dif_log_mean_amount,agg7d2$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
wtd.cor(x=agg7d2$dif_log_mean_amount,y=agg7d2$dif_log_mean_hours_cr0,weight=agg7d2$pct_exp,mean1=F)
table_a0[4,3]<-round(wtd.cor(x=agg7d2$dif_log_mean_amount,y=agg7d2$dif_log_mean_hours_cr0,weight=agg7d2$pct_exp,mean1=F)[1],3)
table_a0[5,3]<-round(wtd.cor(x=agg7d2$dif_log_mean_amount,y=agg7d2$dif_log_mean_hours_cr0,weight=agg7d2$pct_exp,mean1=F)[2],3)
table_a0[6,3]<-paste(ct1$parameter+2)

###
#yearly, contract and in-house, unweighted
ct1<-cor.test(agg67d3$dif_log_mean_amount,agg67d3$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a0[7,1]<-paste(round(ct1$estimate,3))
table_a0[8,1]<-paste(round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3))
table_a0[9,1]<-paste(ct1$parameter+2)
#yearly, contract, unweighted
ct1<-cor.test(agg6d3$dif_log_mean_amount,agg6d3$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a0[7,2]<-paste(round(ct1$estimate,3))
table_a0[8,2]<-paste(round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3))
table_a0[9,2]<-paste(ct1$parameter+2)
#yearly, in-house, unweighted
ct1<-cor.test(agg7d3$dif_log_mean_amount,agg7d3$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a0[7,3]<-paste(round(ct1$estimate,3))
table_a0[8,3]<-paste(round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3))
table_a0[9,3]<-paste(ct1$parameter+2)
#yearly, contract and in-house, weighted
ct1<-cor.test(agg67d3$dif_log_mean_amount,agg67d3$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
wtd.cor(x=agg67d3$dif_log_mean_amount,y=agg67d3$dif_log_mean_hours_cr0,weight=agg67d3$pct_exp,mean1=F)
table_a0[10,1]<-round(wtd.cor(x=agg67d3$dif_log_mean_amount,y=agg67d3$dif_log_mean_hours_cr0,weight=agg67d3$pct_exp,mean1=F)[1],3)
table_a0[11,1]<-round(wtd.cor(x=agg67d3$dif_log_mean_amount,y=agg67d3$dif_log_mean_hours_cr0,weight=agg67d3$pct_exp,mean1=F)[2],3)
table_a0[12,1]<-paste(ct1$parameter+2)
#yearly, contract, weighted
ct1<-cor.test(agg6d3$dif_log_mean_amount,agg6d3$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
wtd.cor(x=agg6d3$dif_log_mean_amount,y=agg6d3$dif_log_mean_hours_cr0,weight=agg6d3$pct_exp,mean1=F)
table_a0[10,2]<-round(wtd.cor(x=agg6d3$dif_log_mean_amount,y=agg6d3$dif_log_mean_hours_cr0,weight=agg6d3$pct_exp,mean1=F)[1],3)
table_a0[11,2]<-round(wtd.cor(x=agg6d3$dif_log_mean_amount,y=agg6d3$dif_log_mean_hours_cr0,weight=agg6d3$pct_exp,mean1=F)[2],3)
table_a0[12,2]<-paste(ct1$parameter+2)
#yearly, in-house, weighted
ct1<-cor.test(agg7d3$dif_log_mean_amount,agg7d3$dif_log_mean_hours_cr0,use="pairwise.complete.obs")
wtd.cor(x=agg7d3$dif_log_mean_amount,y=agg7d3$dif_log_mean_hours_cr0,weight=agg7d3$pct_exp,mean1=F)
table_a0[10,3]<-round(wtd.cor(x=agg7d3$dif_log_mean_amount,y=agg7d3$dif_log_mean_hours_cr0,weight=agg7d3$pct_exp,mean1=F)[1],3)
table_a0[11,3]<-round(wtd.cor(x=agg7d3$dif_log_mean_amount,y=agg7d3$dif_log_mean_hours_cr0,weight=agg7d3$pct_exp,mean1=F)[2],3)
table_a0[12,3]<-paste(ct1$parameter+2)

table_a0<-table_a0[,c("Description","All","Contract","In-House")]
#Appendix A5.2 Results
write.table(data.frame(Description=c("","Table Showing Appendix A5.2 Results:")),row.names=F,col.names=F,quote=F)
print(kbl(table_a0,format="latex"))
print(kbl(table_a0,format="html"))
###Appendix results:


### Appendix Section A5.3
#Results for different organization types
write.table(data.frame(Description=c("","","","Appendix Section A5.3: Correlations for Different Types of Lobbying Clients")),row.names=F,col.names=F,quote=F)
#Create Table A2 
table_a2<-as.data.frame(matrix(NA,nrow=10,ncol=3))
colnames(table_a2)<-c("Correlation","Percent of obs. are contract lobbyists","Description")
table_a2[,3]<-c("Business Entities","","Trade & Professional Associations","","Governmental Entities","","Charitable/Religious/Civic/Oth","","Labor Unions","")

ct1<-cor.test(agg67d2$dif_log_mean_amount[agg67d2$organization_type=="Business Entity"],agg67d2$dif_log_mean_hours_cr0[agg67d2$organization_type=="Business Entity"],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a2[1,1]<-paste(round(ct1$estimate,3))
table_a2[2,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a2[1,2]<-paste(round(length(which(agg67d2$organization_type=="Business Entity"&agg67d2$type=="contract"))/length(which(agg67d2$organization_type=="Business Entity")),3)*100,"%")
table_a2[2,2]<-paste("(out of ",ct1$parameter+2," obs.)",sep="")

ct1<-cor.test(agg67d2$dif_log_mean_amount[agg67d2$organization_type=="Association"],agg67d2$dif_log_mean_hours_cr0[agg67d2$organization_type=="Association"],use="pairwise.complete.obs")
table_a2[3,1]<-paste(round(ct1$estimate,3))
table_a2[4,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a2[3,2]<-paste(round(length(which(agg67d2$organization_type=="Association"&agg67d2$type=="contract"))/length(which(agg67d2$organization_type=="Association")),3)*100,"%")
table_a2[4,2]<-paste("(out of ",ct1$parameter+2," obs.)",sep="")

ct1<-cor.test(agg67d2$dif_log_mean_amount[agg67d2$organization_type=="Governmental"],agg67d2$dif_log_mean_hours_cr0[agg67d2$organization_type=="Governmental"],use="pairwise.complete.obs")
table_a2[5,1]<-paste(round(ct1$estimate,3))
table_a2[6,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a2[5,2]<-paste(round(length(which(agg67d2$organization_type=="Governmental"&agg67d2$type=="contract"))/length(which(agg67d2$organization_type=="Governmental")),3)*100,"%")
table_a2[6,2]<-paste("(out of ",ct1$parameter+2," obs.)",sep="")

ct1<-cor.test(agg67d2$dif_log_mean_amount[agg67d2$organization_type=="Charitable/Religious/Civic/Oth"],agg67d2$dif_log_mean_hours_cr0[agg67d2$organization_type=="Charitable/Religious/Civic/Oth"],use="pairwise.complete.obs")
table_a2[7,1]<-paste(round(ct1$estimate,3))
table_a2[8,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a2[7,2]<-paste(round(length(which(agg67d2$organization_type=="Charitable/Religious/Civic/Oth"&agg67d2$type=="contract"))/length(which(agg67d2$organization_type=="Charitable/Religious/Civic/Oth")),3)*100,"%")
table_a2[8,2]<-paste("(out of ",ct1$parameter+2," obs.)",sep="")

ct1<-cor.test(agg67d2$dif_log_mean_amount[agg67d2$organization_type=="Labor Union"],agg67d2$dif_log_mean_hours_cr0[agg67d2$organization_type=="Labor Union"],use="pairwise.complete.obs")
table_a2[9,1]<-paste(round(ct1$estimate,3))
table_a2[10,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a2[9,2]<-paste(round(length(which(agg67d2$organization_type=="Labor Union"&agg67d2$type=="contract"))/length(which(agg67d2$organization_type=="Labor Union")),3)*100,"%")
table_a2[10,2]<-paste("(out of ",ct1$parameter+2," obs.)",sep="")

#re-order columns
table_a2<-table_a2[,c("Description","Correlation","Percent of obs. are contract lobbyists")]

#Table A2 (Section A5.3)
write.table(data.frame(Description=c("","Table A2: Correlations Between Within-Dyad Changes in Lobbying Expenditures and Hours by 
Type of Organization")),row.names=F,col.names=F,quote=F)
print(kbl(table_a2,format="latex",row.names = F))
print(kbl(table_a2,format="html",row.names = F))
write(kbl(table_a2,format="latex",row.names = F),"output/PSRM_tabA2.tex")
write.table(data.frame(Description=c("(N.B. The file for this table is saved as 'PSRM_tabA2.tex' in the 'output' folder.)")),row.names=F,col.names=F,quote=F)
#correlations for contract and in-house lobbyists across org types


### Appendix Section A5.4
write.table(data.frame(Description=c("","","","Appendix Section A5.4: Correlations for Contract and In-House Lobbyists Across Types of Lob-
bying Clients")),row.names=F,col.names=F,quote=F)
#Create Table A3 
table_a3<-as.data.frame(matrix(NA,nrow=22,ncol=3))
colnames(table_a3)<-c("Contract","In-House","Type of Organization")
table_a3[,3]<-c("Business Entities","","Trade & Professional Associations","","Governmental Entities","","Charitable/Religious/Civic/Oth","","Labor Unions","","Organizations With Only","Contract / In-House Lobbyists","Organizations With Both Contract & In-House","Lobbyists, But Only in Other Sessions","Organizations With Contract ","& In-House Lobbyists in Current Session","Lobbyists With 1 Client","","Lobbyists With up to 4 Clients","(Median # of Clients for Contract Lobbyists)","Lobbyists More Than 4 Clients","(Median # of Clients for Contract Lobbyists)")
ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$organization_type=="Business Entity"],agg6d2$dif_log_mean_hours_cr0[agg6d2$organization_type=="Business Entity"],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[1,1]<-paste(round(ct1$estimate,3))
table_a3[2,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$organization_type=="Business Entity"],agg7d2$dif_log_mean_hours_cr0[agg7d2$organization_type=="Business Entity"],use="pairwise.complete.obs")
table_a3[1,2]<-paste(round(ct1$estimate,3))
table_a3[2,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$organization_type=="Association"],agg6d2$dif_log_mean_hours_cr0[agg6d2$organization_type=="Association"],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[3,1]<-paste(round(ct1$estimate,3))
table_a3[4,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$organization_type=="Association"],agg7d2$dif_log_mean_hours_cr0[agg7d2$organization_type=="Association"],use="pairwise.complete.obs")
table_a3[3,2]<-paste(round(ct1$estimate,3))
table_a3[4,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$organization_type=="Governmental"],agg6d2$dif_log_mean_hours_cr0[agg6d2$organization_type=="Governmental"],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[5,1]<-paste(round(ct1$estimate,3))
table_a3[6,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$organization_type=="Governmental"],agg7d2$dif_log_mean_hours_cr0[agg7d2$organization_type=="Governmental"],use="pairwise.complete.obs")
table_a3[5,2]<-paste(round(ct1$estimate,3))
table_a3[6,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$organization_type=="Charitable/Religious/Civic/Oth"],agg6d2$dif_log_mean_hours_cr0[agg6d2$organization_type=="Charitable/Religious/Civic/Oth"],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[7,1]<-paste(round(ct1$estimate,3))
table_a3[8,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$organization_type=="Charitable/Religious/Civic/Oth"],agg7d2$dif_log_mean_hours_cr0[agg7d2$organization_type=="Charitable/Religious/Civic/Oth"],use="pairwise.complete.obs")
table_a3[7,2]<-paste(round(ct1$estimate,3))
table_a3[8,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$organization_type=="Labor Union"],agg6d2$dif_log_mean_hours_cr0[agg6d2$organization_type=="Labor Union"],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[9,1]<-paste(round(ct1$estimate,3))
table_a3[10,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$organization_type=="Labor Union"],agg7d2$dif_log_mean_hours_cr0[agg7d2$organization_type=="Labor Union"],use="pairwise.complete.obs")
table_a3[9,2]<-paste(round(ct1$estimate,3))
table_a3[10,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
###
# contract never in-house, ever in house and session in-house vs. ever in-house and no-session in-house

ct1<-cor.test(agg6d2$dif_log_mean_amount[which(agg6d2$both_ever_count1==0)],agg6d2$dif_log_mean_hours_cr0[which(agg6d2$both_ever_count1==0)],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[11,1]<-paste(round(ct1$estimate,3))
table_a3[12,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[which(agg6d2$both_ever_count1==1&agg6d2$inhouse_session_count1==0)],agg6d2$dif_log_mean_hours_cr0[which(agg6d2$both_ever_count1==1&agg6d2$inhouse_session_count1==0)],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[13,1]<-paste(round(ct1$estimate,3))
table_a3[14,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$both_ever_count1==1&agg6d2$inhouse_session_count1==1],agg6d2$dif_log_mean_hours_cr0[agg6d2$both_ever_count1==1&agg6d2$inhouse_session_count1==1],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[15,1]<-paste(round(ct1$estimate,3))
table_a3[16,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$both_ever_count1==0],agg7d2$dif_log_mean_hours_cr0[agg7d2$both_ever_count1==0],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[11,2]<-paste(round(ct1$estimate,3))
table_a3[12,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$both_ever_count1==1&agg7d2$contract_session_count1==0],agg7d2$dif_log_mean_hours_cr0[agg7d2$both_ever_count1==1&agg7d2$contract_session_count1==0],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[13,2]<-paste(round(ct1$estimate,3))
table_a3[14,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$both_ever_count1==1&agg7d2$contract_session_count1==1],agg7d2$dif_log_mean_hours_cr0[agg7d2$both_ever_count1==1&agg7d2$contract_session_count1==1],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[15,2]<-paste(round(ct1$estimate,3))
table_a3[16,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

#get median number of clients per contract lobbyist
lac<-loball5ciA%>%
  group_by(lobname_cleaned,type,halfyear) %>% 
  summarise(.groups="keep",count_clients=sum(count))
median(lac$count_clients[lac$type=="contract"]) #4

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$count_client==1],agg6d2$dif_log_mean_hours_cr0[agg6d2$count_client==1],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[17,1]<-paste(round(ct1$estimate,3))
table_a3[18,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg7d2$dif_log_mean_amount[agg7d2$count_client==1],agg7d2$dif_log_mean_hours_cr0[agg7d2$count_client==1],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[17,2]<-paste(round(ct1$estimate,3))
table_a3[18,2]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$count_client<=4],agg6d2$dif_log_mean_hours_cr0[agg6d2$count_client<=4],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[19,1]<-paste(round(ct1$estimate,3))
table_a3[20,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")

ct1<-cor.test(agg6d2$dif_log_mean_amount[agg6d2$count_client>4],agg6d2$dif_log_mean_hours_cr0[agg6d2$count_client>4],use="pairwise.complete.obs")
paste(round(ct1$estimate,3)," (", round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")
table_a3[21,1]<-paste(round(ct1$estimate,3))
table_a3[22,1]<-paste("(",round(sqrt( (1-ct1$estimate^2)/(ct1$parameter)),3),")",sep="")


table_a3<-table_a3[,c("Type of Organization","Contract","In-House")]
table_a3[is.na(table_a3$`In-House`),3]<-"" #change from NA to ""
#Table A3 (Section A5.4)
write.table(data.frame(Description=c("","Table A3: Correlations Between Within-Dyad Changes in Lobbying Expenditures and Hours for 
Contract and In-House Lobbyists by Type of Organization")),row.names=F,col.names=F,quote=F)
print(kbl(table_a3,format="latex",row.names = F))
print(kbl(table_a3,format="html",row.names = F))
write(kbl(table_a3,format="latex",row.names = F),"output/PSRM_tabA3.tex")
write.table(data.frame(Description=c("(N.B. The file for this table is saved as 'PSRM_tabA3.tex' in the 'output' folder.)")),row.names=F,col.names=F,quote=F)


